package com.exam.api.modules.exampaper.mapper;

import com.exam.api.modules.exampaper.vo.ExamPaperStatisticsVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * TblExamPaperMapper
 */
@Mapper
public interface TblExamPaperStatisticsMapper {

    //. 统计每份试卷的整体情况（参与人数、平均分、最高/最低分）
    @Select("SELECT ep.id AS v1,ep.NAME AS v2,COUNT(DISTINCT ur.user_id) AS v3,ROUND(AVG(ur.score),2) AS v4,MAX(ur.score) AS v5,MIN(ur.score) AS v6 FROM tbl_exam_paper ep INNER JOIN tbl_user_answer_record ur ON ep.id=ur.paper_id AND ur.is_delete=0 WHERE ep.is_delete=0 AND ep.STATUS=1-- 只统计已发布的有效试卷\n" +
            "GROUP BY ep.id,ep.NAME,ep.publish_time ORDER BY ep.publish_time DESC;")
    public List<ExamPaperStatisticsVO> selectStatistics01();

    @Select("SELECT pq.NO AS v1,pq.score AS v2,ROUND(AVG(ur.score),2) AS v3,ROUND(AVG(ur.score)/pq.score*100,2) AS v4 FROM tbl_exam_paper_question pq INNER JOIN tbl_user_answer_record ur ON pq.id=ur.question_id AND ur.is_delete=0 WHERE pq.is_delete=0  AND pq.exam_paper_id = #{examPaperId}\n" +
            "GROUP BY pq.exam_paper_id,pq.id,pq.NO,pq.question_type,pq.score ORDER BY pq.exam_paper_id,pq.NO")
    public List<ExamPaperStatisticsVO> selectStatistics02(@Param("examPaperId") Long examPaperId);

    @Select("SELECT (\n" +
            "SELECT COUNT(1) FROM tbl_student) AS v1,(\n" +
            "SELECT COUNT(1) FROM tbl_class_number_info) AS v2,(\n" +
            "SELECT COUNT(1) FROM tbl_exam_paper) AS v3,(\n" +
            "SELECT COUNT(1) FROM (\n" +
            "SELECT COUNT(1) FROM tbl_user_answer_record GROUP BY paper_id,user_id) t) AS v4 FROM DUAL")
    public ExamPaperStatisticsVO selectStatisticsT();
}